package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.model.PoolAbilityInfo;

import core.CoreDao;
import core.db.DBConnectionUtil;

/**
 * 能力号码配置关系dao
 * 
 * @author liyalei
 * @version 2016年3月2日 下午7:03:53
 * 
 */
public class PoolAbilityInfoDao {
    private static final Logger log = core.util.Logger.getLogger(PoolAbilityInfoDao.class.getSimpleName());

    /**
     * 新建PoolAbilityInfo信息
     * 
     * @param dao
     * @param poolAbility
     * @return 成功返回0，失败返回-1
     */
    public int insert(CoreDao dao, PoolAbilityInfo poolAbility) {
	log.info("PoolAbilityInfoDao.insert");
	// 数据库连接
	Connection con = null;
	int result = -1;
	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 执行sql拼写
	    StringBuffer sql = new StringBuffer("INSERT INTO t_pool_ability_info(pool_id, ability_type, display_number, charge_number, charge_key, reserve_column1, reserve_column2, comment_info, limit_num, create_time, update_time, create_user, update_user)");
	    sql.append(" VALUES(?,?,?,?,?,?,?,?,?,sysdate(),sysdate(),?,?)");
	    log.info("insert.sql=" + sql);
	    // 执行sql
	    List<JSONObject> list = dao.serviceJsonSql(con,sql.toString(),
		    new Object[] { poolAbility.getPool_id(), poolAbility.getAbility_type(), poolAbility.getDisplay_number(), poolAbility.getCharge_number(),
			    poolAbility.getCharge_key(), poolAbility.getReserve_column1(), poolAbility.getReserve_column2(), poolAbility.getComment_info(),
			    poolAbility.getLimit_num(), poolAbility.getCreate_user(), poolAbility.getUpdate_user() });

	    JSONObject obj = list.get(0);
	    result = obj.optInt("result");

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 编辑PoolAbilityInfo信息
     * 
     * @param dao
     * @param poolAbility
     * @return 成功返回0，失败返回-1
     */
    public int update(CoreDao dao, PoolAbilityInfo poolAbility) {
	log.info("PoolAbilityInfoDao.update");
	// 数据库连接
	Connection con = null;
	int result = -1;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写SQL
	    StringBuffer sql = new StringBuffer(" UPDATE t_pool_ability_info set pool_id=?,ability_type=?,display_number=?,charge_number=?,charge_key=?,reserve_column1=?,reserve_column2=?,comment_info=?,limit_num=?,update_time=?,update_user=?");
	    sql.append(" where id=?");
	    log.info("update.sql=" + sql);
	    // 执行sql，更新相应数据
	    List<JSONObject> list = dao.serviceJsonSql(con,sql.toString(),
		    new Object[] { poolAbility.getPool_id(), poolAbility.getAbility_type(), poolAbility.getDisplay_number(), poolAbility.getCharge_number(),
			    poolAbility.getCharge_key(), poolAbility.getReserve_column1(), poolAbility.getReserve_column2(), poolAbility.getComment_info(),
			    poolAbility.getLimit_num(), poolAbility.getUpdate_time(), poolAbility.getUpdate_user(), poolAbility.getId() });

	    if (list != null && list.size() > 0) {
		JSONObject obj = list.get(0);
		result = obj.optInt("result");
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 查询所有PoolNumber信息
     * 
     * @param dao
     * @param pageSql
     * @param paramSql
     * @return
     */
    public List<PoolAbilityInfo> queryAllPoolAbility(CoreDao dao, String pageSql, String paramSql) {
	log.info("PoolAbilityInfoDao.queryAllPoolAbility");
	// 数据库连接
	Connection con = null;
	// 最后需返回的
	List<PoolAbilityInfo> result = null;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写sql
	    StringBuffer sql = new StringBuffer("SELECT tp.id,tp.pool_id,tp.ability_type,tp.display_number,tp.charge_number,tp.charge_key,tp.reserve_column1,tp.reserve_column2,tp.comment_info,tp.limit_num,tp.create_time,tp.update_time,tp.create_user,tp.update_user");
	    sql.append(" FROM t_pool_ability_info tp");

	    // 判断如果查询条件不为空，则拼写sql
	    if (StringUtils.isNotEmpty(paramSql)) {
		sql.append(" WHERE ");
		sql.append(paramSql);
	    }
	    sql.append(" ORDER BY tp.create_time DESC ");
	    sql.append(pageSql);
	    log.info("queryAllPoolAbility=" + sql);
	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		result = items;
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;

    }

    /**
     * 获取号码池信息的条数
     * 
     * @param dao
     * @param paramSql
     * @return
     */
    public int getTotalPoolAbility(CoreDao dao, String paramSql) {
	log.info("PoolAbilityInfoDao.getTotalPoolAbility");
	if (StringUtils.isNotBlank(paramSql)) {
	    paramSql = " and " + paramSql;
	} else {
	    paramSql = "";
	}
	Connection con = null;
	int result = -1;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("select count(1) as totalPoolAbility from t_pool_ability_info tp where 1=1");
	    sql.append(paramSql);

	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] {});

	    JSONObject obj = list.get(0);
	    result = (Integer) obj.get("totalPoolAbility");
	    log.info("getTotalPoolAbility=" + sql.toString());

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据号码池中号码的id，获取该号码已配置的号码能力关系
     * 
     * @param dao
     * @param poolId
     * @return
     */
    public List<PoolAbilityInfo> getRelByPoolId(CoreDao dao, String poolId) {
	log.info("PoolAbilityInfoDao.getRelByPoolId");
	Connection con = null;
	List<PoolAbilityInfo> result = null;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("SELECT * FROM t_pool_ability_info t WHERE t.pool_id=?");

	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { poolId });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		result = items;
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据账号id，查询该账号下所有已配置的号码，它们所配置的能力
     * 
     * @param dao
     * @param accountId
     *            账号id
     * @return
     */
    public List<PoolAbilityInfo> getAllAbiByAccId(CoreDao dao, String cust_account, String app_key, String abilityType) {
	log.info("PoolAbilityInfoDao.getAllAbiByAccId");
	Connection con = null;
	List<PoolAbilityInfo> result = null;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");

	    StringBuffer sql = new StringBuffer("SELECT t2.tpa_id AS id,t2.phone_number,t2.pool_id,t2.ability_type,t2.number_vest_name FROM(");
	    sql.append(" SELECT DISTINCT tpn.id FROM t_pool_number tpn,t_pool_ability_info tpa,t_pool_account_rel tpr");
	    sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpn.id=tpa.pool_id AND tpr.cust_account=? and tpr.app_key=?) t1");
	    sql.append(" INNER JOIN (");
	    sql.append(" SELECT tpa.id AS tpa_id,tpa.pool_id,tpn.phone_number,tpa.ability_type,tpn.number_vest_name FROM t_pool_ability_info tpa,t_pool_number tpn");
	    sql.append(" WHERE tpa.pool_id=tpn.id AND tpa.ability_type=?)t2");
	    sql.append(" ON t1.id=t2.pool_id");

	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { cust_account, app_key, abilityType });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		result = items;
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据能力类型和账号id，获取该账号下已配置该号码能力的ability表的id
     * 
     * @param dao
     * @param cust_account
     *            账号标识
     * @param app_key
     *            应用标识
     * @param abilityType
     *            能力类型
     * @return
     */
    public List<PoolAbilityInfo> getAbiIdByAccIdAbi(CoreDao dao, String cust_account, String app_key, String abilityType) {
	log.info("PoolAbilityInfoDao.getAbiIdByAccIdAbi");
	Connection con = null;
	List<PoolAbilityInfo> result = null;
	try {
	    con = DBConnectionUtil.getDBConnection("emate1");
	    StringBuffer sql = new StringBuffer("SELECT DISTINCT tpa.id FROM t_pool_ability_info tpa,t_pool_account_rel tpr");
	    sql.append(" WHERE tpa.id=tpr.pool_num_id");
	    sql.append(" AND tpr.cust_account=? and tpr.app_key=? AND tpa.ability_type=?");

	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { cust_account, app_key, abilityType });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		result = items;
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 查询所有PoolNumber号码信息
     * 
     * @param dao
     * @return
     */
    public List<PoolAbilityInfo> queryAllPoolAbilityNumber(CoreDao dao) {
	log.info("PoolAbilityInfoDao.queryAllPoolAbilityNumber");
	// 数据库连接
	Connection con = null;
	// 最后需返回的
	List<PoolAbilityInfo> result = null;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写sql
	    StringBuffer sql = new StringBuffer("SELECT DISTINCT t.charge_number,t.charge_key FROM t_pool_ability_info t WHERE t.ability_type<>'ScheduleConference'");

	    log.info("queryAllPoolAbilityNumber=" + sql);
	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		result = items;
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;

    }
    /**
     * 查询默认显号
     * @param dao
     * @param ability_type  能力
     * @param number_source 来源（号码来源：通道提供：external，用户提供：custom，内部号码：inside,公共号码池：general）
     * @return
     */
    public PoolAbilityInfo queryDefaultPoolAbilityNumber(CoreDao dao, String ability_type, String number_source) {
	// 数据库连接
	Connection con = null;
	// 最后需返回的
	PoolAbilityInfo result = null;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写sql
	    StringBuffer sql = new StringBuffer(
		    "SELECT ti.id, pool_id, ability_type, display_number, charge_number, charge_key, reserve_column1, reserve_column2, comment_info, limit_num, ti.create_time, ti.update_time, ti.create_user, ti.update_user ");
	    sql.append("FROM t_pool_ability_info ti, t_pool_number n where 1=1 ");
	    sql.append("AND ti.pool_id=n.id AND n.number_source=? ");
	    sql.append("AND ti.ability_type=? ");

	    log.info("queryPoolAbilityNumberById=" + sql.toString());
	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { number_source,ability_type });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		if (items.size() > 0) {
		    int index=(int)(Math.random()*items.size());
		    result=items.get(index);
		}
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;
    }

    /**
     * 根据能力号码ID查询相应信息
     * 
     * @param dao
     * @param pool_ability_id
     * @return
     */
    public PoolAbilityInfo queryPoolAbilityNumberById(CoreDao dao, String pool_ability_id) {
	log.info("PoolAbilityInfoDao.queryPoolAbilityNumberById");
	// 数据库连接
	Connection con = null;
	// 最后需返回的
	PoolAbilityInfo result = null;

	try {
	    // 获取数据库连接
	    con = DBConnectionUtil.getDBConnection("emate1");
	    // 拼写sql
	    StringBuffer sql = new StringBuffer("SELECT id, pool_id, ability_type, display_number, charge_number, charge_key, reserve_column1, reserve_column2, comment_info, limit_num, create_time, update_time, create_user, update_user ");
	    sql.append("FROM t_pool_ability_info WHERE id=?");

	    log.info("queryPoolAbilityNumberById=" + sql.toString());
	    // 查询数据
	    List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { pool_ability_id });
	    // 将json类型转化为NumberPool
	    if (list != null) {
		List<PoolAbilityInfo> items = dao.covertToBean(list, PoolAbilityInfo.class);
		if (items.size() > 0) {
		    result = items.get(0);
		}
	    }

	} catch (Exception ex) {
	    ex.printStackTrace();
	} finally {
	    DBConnectionUtil.close(null, null, con);
	}
	return result;

    }
}
